import sqlite3


class Database:

    def __init__(self, dbpath):
        # connection = sqlite3.connect('./database.db')
        self.connection = sqlite3.connect(dbpath)
        self.cursor = self.connection.cursor()

    def __del__(self):
        self.connection.commit()
        self.connection.close()

    def execute(self, sql, argtuple):
        try:
            self.cursor.execute(sql,argtuple)
            self.connection.commit()
            return [True, ]
        except sqlite3.OperationalError as err:
            err = str(err)
            errlist = [False, err]
            return errlist

    def getshelfidbyname(self, name):
        return self.fetchone('SELECT id FROM shelves WHERE name="' + name + '"')[0]

    def fetchall(self, sql=''):
        # 'SELECT * FROM shelves'
        self.cursor.execute(sql)
        rows = self.cursor.fetchall()
        return rows

    def fetchone(self, sql=''):
        if sql == '':
            row = self.cursor.fetchone()
        else:
            self.cursor.execute(sql)
            row = self.cursor.fetchone()
        if row:
            return row
        else:
            return False

    def count(self, sql='SELECT COUNT(id) FROM shelves'):
        self.cursor.execute(sql)
        row = self.cursor.fetchone()
        if row:
            return row


if __name__ == '__main__':
    print('class Database tests:')
    a = Database('./database.db')
    print(a.fetchall('SELECT * FROM shelves'))
    print(a.fetchone('SELECT * FROM shelves'))
    print(a.fetchone())
    print(a.fetchone())
    print(a.fetchone())
    print(a.fetchone())
    print(a.fetchone())
    print(a.fetchone())
    print(a.count())
    print(a.execute("SELECT * FROM shelves", ()))  # 查询成功返回结果测试

    del a
